package com.cennavi.mineplay.data;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.cennavi.mineplay.gisfile.CsvReader;
import com.cennavi.mineplay.gisfile.GeojsonReader;
import com.cennavi.mineplay.gisfile.MifReader;
import com.cennavi.mineplay.gisfile.ShapeReader;
import com.cennavi.mineplay.utils.ZipUtil;

@RestController
@RequestMapping(value = "/data")
public class DataUpload {

	@Value("${upload.path}")
	private String uploadPath;
	
	
	@Value("${upload.image_path}")
	private String uploadImagePath;
	
	
	
	@Value("${url.uploadimage}")
	private String urlUploadImagePath;
	
	@Autowired
	protected JdbcTemplate jdbc;
	
	@Value("${webservice.prefix}")
	private String serviceUrlfPrefix;

	@RequestMapping(value = "/upload/shp", method = RequestMethod.POST)
	public @ResponseBody Object uploadShp(
			@RequestParam("dbf") MultipartFile dbf,
			@RequestParam("prj") MultipartFile prj,
			@RequestParam("shp") MultipartFile shp,
			@RequestParam("shx") MultipartFile shx,
			String describe,
			HttpServletRequest request,
			String user_id) {
		
		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(dbf.getBytes(), tmpDir, dbf.getOriginalFilename());
			this.uploadFile(prj.getBytes(), tmpDir, prj.getOriginalFilename());
			this.uploadFile(shp.getBytes(), tmpDir, shp.getOriginalFilename());
			this.uploadFile(shx.getBytes(), tmpDir, shx.getOriginalFilename());
			
			
			String shpPath = tmpDir +"/" + shx.getOriginalFilename();
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			ShapeReader reader = new ShapeReader(shpPath, tableName, conn);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe) values (?,?)";
			
			jdbc.update(sql, tableName,describe);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	
	
	
	@RequestMapping(value = "/upload/shp/zip", method = RequestMethod.POST)
	public @ResponseBody Object uploadShpZip(
			@RequestParam("zip") MultipartFile zipName,
			String describe,
			HttpServletRequest request,
			String user_id) {
		
		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(zipName.getBytes(), tmpDir, zipName.getOriginalFilename());
			
			ZipUtil.unZip(tmpDir+"/"+zipName.getOriginalFilename(), tmpDir);
			
			File[] files = new File(tmpDir).listFiles();
			
			String shxName = null;
			
			for(File f: files){
				if (f.getName().endsWith("shx")){
					shxName = f.getName();
					break;
				}
			}
			
			
			
			String shpPath = tmpDir +"/" + shxName;
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			ShapeReader reader = new ShapeReader(shpPath, tableName, conn);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe,user_id) values (?,?,?)";
			
			jdbc.update(sql, tableName,describe,user_id);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	
	
	@RequestMapping(value = "/upload/mif", method = RequestMethod.POST)
	public @ResponseBody Object uploadMif(
			@RequestParam("mif") MultipartFile mif,
			@RequestParam("mid") MultipartFile mid,
			String describe,
			 HttpServletRequest request,
				String user_id) {

		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(mif.getBytes(), tmpDir, mif.getOriginalFilename());
			this.uploadFile(mid.getBytes(), tmpDir, mid.getOriginalFilename());
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			String mifPath = tmpDir+"/" + mif.getOriginalFilename();
			
			MifReader reader = new MifReader(mifPath, conn, tableName);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe) values (?,?)";
			
			jdbc.update(sql, tableName,describe);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	
	
	
	
	@RequestMapping(value = "/upload/mif/zip", method = RequestMethod.POST)
	public @ResponseBody Object uploadMifZip(
			@RequestParam("zip") MultipartFile zipName,
			String describe,
			 HttpServletRequest request,
				String user_id) {

		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(zipName.getBytes(), tmpDir, zipName.getOriginalFilename());
			
			ZipUtil.unZip(tmpDir+"/"+zipName.getOriginalFilename(), tmpDir);
			
			File[] files = new File(tmpDir).listFiles();
			
			String mifName = null;
			
			for(File f: files){
				if (f.getName().toLowerCase().endsWith("mif")){
					mifName = f.getName();
					break;
				}
			}
			
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			String mifPath = tmpDir+"/" + mifName;
			
			MifReader reader = new MifReader(mifPath, conn, tableName);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe,user_id) values (?,?,?)";
			
			jdbc.update(sql, tableName,describe,user_id);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	
	
	
	
	
	
	@RequestMapping(value = "/upload/csv", method = RequestMethod.POST)
	public @ResponseBody Object uploadCsv(
			@RequestParam("csv") MultipartFile csv,
			String describe,
			String type,
			 HttpServletRequest request,
				String user_id) {

		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(csv.getBytes(), tmpDir, csv.getOriginalFilename());
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			String csvPath = tmpDir+"/" + csv.getOriginalFilename();
			
			CsvReader reader = new CsvReader(tableName, conn, csvPath,type);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe,user_id) values (?,?,?)";
			
			jdbc.update(sql, tableName,describe,user_id);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	

	@RequestMapping(value = "/upload/geojson", method = RequestMethod.POST)
	public @ResponseBody Object uploadGeojson(
			@RequestParam("geojson") MultipartFile csv,
			String describe,
			 HttpServletRequest request,
				String user_id) {

		long timeFlag = new Date().getTime();

		String tmpDir = uploadPath + "/" + timeFlag;
		
		String tableName = "upload_"+ timeFlag;

		try {
			this.uploadFile(csv.getBytes(), tmpDir, csv.getOriginalFilename());
			
			Connection conn = jdbc.getDataSource().getConnection();
			
			String csvPath = tmpDir+"/" + csv.getOriginalFilename();
			
//			CsvReader reader = new CsvReader(tableName, conn, csvPath,type);
			
			GeojsonReader reader = new GeojsonReader(csvPath, tableName, conn);
			
			reader.createTable();
			
			reader.batchInsert();
			
			conn.close();
			
			String sql = "insert into table_list(name,describe,user_id) values (?,?,?)";
			
			jdbc.update(sql, tableName,describe,user_id);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.renderModelList(tableName);
	}
	
	
	
	@RequestMapping(value = "/upload/image", method = RequestMethod.POST)
	public @ResponseBody Object uploadImage(
			@RequestParam("image") MultipartFile img
			) {

		String timeFlag = new Date().getTime() +".png";

		String tmpDir = uploadImagePath + "/";
		
		try {
			this.uploadFile(img.getBytes(), tmpDir, timeFlag);
			
			String sql = "insert into upload_image_data(image_name) values (?)";
			
			jdbc.update(sql, timeFlag);
			
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return this.uploadImageList();
	}
	
	@RequestMapping(value="/upload/chart/data", method = RequestMethod.POST) 
	public @ResponseBody String uploadChartData(@RequestParam("data") MultipartFile chartData) {
		
		String timeFlag = new Date().getTime()+"";
		
		String tmpDir = uploadPath + "/" + timeFlag;
		
		try {
			this.uploadFile(chartData.getBytes(), tmpDir, timeFlag);
			
			Scanner scanner = new Scanner(chartData.getInputStream());
			
			StringBuilder sb = new StringBuilder();
			
			while(scanner.hasNextLine()) {
				sb.append(scanner.nextLine());
			}
			
			scanner.close();
			
			String sql = "INSERT INTO upload_chart_data(id,value) VALUES(?,?)";
			jdbc.update(sql, timeFlag, sb.toString());
			
			String url = serviceUrlfPrefix +"/data/chart/data?id="+timeFlag;
			
			return url;
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		
		return null;
	}
	
	
	@RequestMapping(value="/chart/data") 
	public @ResponseBody String chartData(@RequestParam("id") String id) {
		 
		try {
			
			String sql = "SELECT value FROM upload_chart_data WHERE id=?";
			Map<String,Object> map = jdbc.queryForMap(sql,id);
			
			return map.get("value").toString();
			 
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return null;
	}
	
	@RequestMapping(value = "/upload/txt/data", method = RequestMethod.POST)
	public @ResponseBody String uploadTxtData(@RequestParam("data") MultipartFile txtData) {
		
			String timeFlag = new Date().getTime() + "";
			String tmpDir = uploadPath + "/" +timeFlag;
			
			try {
				this.uploadFile(txtData.getBytes(), tmpDir, timeFlag);
				Scanner scanner = new Scanner(txtData.getInputStream());
				StringBuilder sb = new StringBuilder();
				while(scanner.hasNextLine()) {
					sb.append(scanner.nextLine());
				}
				scanner.close();
				
				String sql = "INSERT INTO upload_txt_data(id, value) VALUES(?, ?)";
				jdbc.update(sql, timeFlag, sb.toString());
				String url = serviceUrlfPrefix +"/data/txt/data?id="+timeFlag;
				return url;
			} catch(IOException e) {
				e.printStackTrace();
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			return null;
	}
	
	@RequestMapping(value="/txt/data")
	public @ResponseBody String txtData(@RequestParam("id") String id) {
		try {
		String sql = "SELECT value FROM upload_txt_data WHERE id = ?";
		Map<String, Object> map = jdbc.queryForMap(sql, id);
		return map.get("value").toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	} 
	
	@RequestMapping(value = "/upload/image_list")
	public Object uploadImageListService() {
		return uploadImageList();
	}
	
	
	private Object uploadImageList(){
		
		String sql = "select image_name from upload_image_data";
		
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		
		List<String> list2 = new ArrayList<String>();
		
		for(Map<String,Object> m: list){
			list2.add(urlUploadImagePath+m.get("image_name").toString());
		}
		
		return list2;
	}
	
 
	
	private Object renderModelList(String table_name) {
		
//		String geomSql = "select st_astext(geom) as wkt from "+ table_name +" limit 1";
//		
//		List<Map<String, Object>> listGeomType = jdbc.queryForList(geomSql);
//		
//		if (listGeomType == null || listGeomType.size()==0)
//			return new ArrayList<String>();
//		
//		String wkt = listGeomType.get(0).toString();
//		
//		String geom_type = null;
//		
//		if (wkt.indexOf("POINT")>=0)
//			geom_type= "point";
//		else if (wkt.indexOf("LINE")>=0)
//			geom_type = "line";
//		else
//			geom_type = "polygon";
//		
//		String sql = "select * from render_model_list where geom_type=?";
//
//		try {
//			
//			List<Map<String, Object>> list = jdbc.queryForList(sql,geom_type);
//			
//			for(Map<String,Object> map : list){
//				
//				
//				map.put("image_url", serviceUrlfPrefix+"/image/render/"+map.get("image_url"));
//				if (map.get("service_url")==null){
//					String serviceType = map.get("service_type").toString();
//					if ("PBF".equals(serviceType)){
//						map.put("service_url", serviceUrlfPrefix+"/data/pbf/{z}/{x}/{y}");
//					}else if ("GEOJSON".equals(serviceType)){
//						map.put("service_url", serviceUrlfPrefix+"/data/geojson");
//					}
//				}else{
//					map.put("service_url", serviceUrlfPrefix+map.get("service_url"));
//				}
//			}
//			
//			Map<String,Object> map = new HashMap<>();
//			
//			map.put("table_name", table_name);
//			
//			map.put("models", list);
//			
//			return map;
//
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//
//		return null;
		
		

		
		String geomSql = "select st_astext(geom) as wkt from "+ table_name +" limit 1";
		
		List<Map<String, Object>> listGeomType = jdbc.queryForList(geomSql);
		
		if (listGeomType == null || listGeomType.size()==0)
			return new ArrayList<String>();
		
		String wkt = listGeomType.get(0).toString();
		
		String geom_type = null;
		
		if (wkt.indexOf("POINT")>=0)
			geom_type= "point";
		else if (wkt.indexOf("LINE")>=0)
			geom_type = "line";
		else
			geom_type = "polygon";
		
		String sql = "select * from render_model_list where geom_type=?";

		try {
			
			List<Map<String, Object>> list = jdbc.queryForList(sql,geom_type);
			
			for(Map<String,Object> map : list){
				
				
				map.put("image_url", serviceUrlfPrefix+"/image/render/"+map.get("image_url"));
				if (map.get("service_url")==null){
					String serviceType = map.get("service_type").toString();
					String type = map.get("type").toString();
					if ("particleEffect".equals(type)){
						map.put("service_url", serviceUrlfPrefix+"/data/pbf_particle/{z}/{x}/{y}");
					}else if ("hexagon".equals(type)){
						map.put("service_url", serviceUrlfPrefix+"/data/pbf/hexgrid/{z}/{x}/{y}");
					}else if ("grid".equals(type)){
						map.put("service_url", serviceUrlfPrefix+"/data/pbf/grid/{z}/{x}/{y}");
					}else if ("grid_num".equals(type)){
						map.put("service_url", serviceUrlfPrefix+"/data/pbf/grid_num/{z}/{x}/{y}");
					}else if ("PBF".equals(serviceType)){
						map.put("service_url", serviceUrlfPrefix+"/data/pbf/{z}/{x}/{y}");
					}else if ("GEOJSON".equals(serviceType)){
						map.put("service_url", serviceUrlfPrefix+"/data/geojson");
					}
				}else{
					map.put("service_url", serviceUrlfPrefix+map.get("service_url"));
				}
			}
			
			Map<String,Object> map = new HashMap<>();
			
			map.put("table_name", table_name);
			
			map.put("models", list);
			
			return map;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	
	}
	
	

	private void uploadFile(byte[] file, String filePath, String fileName)
			throws Exception {

		File targetFile = new File(filePath);
		if (!targetFile.exists()) {
			targetFile.mkdirs();
		}
		FileOutputStream out = new FileOutputStream(filePath +"/"+ fileName);
		out.write(file);
		out.flush();
		out.close();
	}

}
